export interface Scenario {
  id: string;
  title: string;
  description: string;
  setupQueries: string[];
  tasks: Task[];
}

export interface Task {
  id: string;
  title: string;
  description: string;
  hint?: string;
  expectedPattern?: RegExp;
  sampleSolution: string;
  validation?: (result: any) => { valid: boolean; message?: string };
}

export const eCommerceScenario: Scenario = {
  id: "ecommerce",
  title: "电子商务数据库",
  description: "在这个场景中，我们将创建一个简单的电子商务数据库，包含用户、产品和订单信息。",
  setupQueries: [
    `CREATE TABLE users (
      id INTEGER PRIMARY KEY,
      username TEXT NOT NULL UNIQUE,
      email TEXT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );`,

    `CREATE TABLE products (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      description TEXT,
      price REAL NOT NULL,
      stock INTEGER NOT NULL DEFAULT 0
    );`,

    `CREATE TABLE orders (
      id INTEGER PRIMARY KEY,
      user_id INTEGER NOT NULL,
      order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      status TEXT DEFAULT 'pending',
      FOREIGN KEY (user_id) REFERENCES users(id)
    );`,

    `CREATE TABLE order_items (
      id INTEGER PRIMARY KEY,
      order_id INTEGER NOT NULL,
      product_id INTEGER NOT NULL,
      quantity INTEGER NOT NULL,
      price REAL NOT NULL,
      FOREIGN KEY (order_id) REFERENCES orders(id),
      FOREIGN KEY (product_id) REFERENCES products(id)
    );`,

    // 插入示例数据 - 使用 INSERT OR IGNORE 来避免唯一约束错误
    `INSERT OR IGNORE INTO users (username, email) VALUES 
      ('user1', 'user1@example.com'),
      ('user2', 'user2@example.com'),
      ('user3', 'user3@example.com');`,

    `INSERT OR IGNORE INTO products (name, description, price, stock) VALUES
      ('笔记本电脑', '高性能笔记本电脑', 5999.99, 10),
      ('智能手机', '最新款智能手机', 3999.99, 20),
      ('平板电脑', '轻薄触控平板', 2499.99, 15),
      ('耳机', '无线蓝牙耳机', 499.99, 50),
      ('键盘', '机械键盘', 299.99, 30);`,

    `INSERT OR IGNORE INTO orders (user_id, status) VALUES
      (1, 'completed'),
      (2, 'processing'),
      (1, 'pending');`,

    `INSERT OR IGNORE INTO order_items (order_id, product_id, quantity, price) VALUES
      (1, 1, 1, 5999.99),
      (1, 3, 1, 2499.99),
      (1, 4, 1, 499.99),
      (2, 2, 1, 3999.99),
      (3, 5, 2, 299.99);`
  ],
  tasks: [
    {
      id: "select-all-users",
      title: "查询所有用户",
      description: "编写一个SQL查询来获取所有用户的信息。",
      sampleSolution: "SELECT * FROM users;",
      validation: (result) => {
        return {
          valid: result.rows.length === 3,
          message: result.rows.length === 3 ? "成功查询到所有用户！" : "应该返回3个用户记录。"
        };
      }
    },
    {
      id: "select-products-price",
      title: "查询特定价格范围的产品",
      description: "查询价格在1000元到5000元之间的所有产品。",
      hint: "使用WHERE子句和BETWEEN操作符。",
      sampleSolution: "SELECT * FROM products WHERE price BETWEEN 1000 AND 5000;",
      validation: (result) => {
        return {
          valid: result.rows.length === 2,
          message: result.rows.length === 2 ? "成功查询到价格范围内的产品！" : "应该返回2个产品记录。"
        };
      }
    },
    {
      id: "join-orders-users",
      title: "连接订单和用户",
      description: "编写一个查询，显示每个订单的ID、订单日期、状态以及下单用户的用户名。",
      hint: "使用JOIN子句连接orders和users表。",
      sampleSolution: `
        SELECT o.id, o.order_date, o.status, u.username
        FROM orders o
        JOIN users u ON o.user_id = u.id;
      `,
      validation: (result) => {
        return {
          valid: result.rows.length === 3,
          message: result.rows.length === 3 ? "成功连接订单和用户表！" : "应该返回3个订单记录。"
        };
      }
    }
  ]
};

export const scenarios = [eCommerceScenario]; 